﻿CREATE FUNCTION [dbo].[VinCheck](
	@VIN varchar(18)
)RETURNS bit WITH SCHEMABINDING AS
--DECLARE @Ret bit, @VIN varchar(17)SET @VIN = '1b3es47y6wd697003'--1FTYR14CXXPB65770'--
BEGIN

IF LEN(@VIN) = 9 OR @VIN LIKE '%[_]%' RETURN 1
IF LEN(@VIN) <> 17 RETURN 0

DECLARE @Chars TABLE(Pos int IDENTITY,Char char(1),Weight tinyint)
DECLARE @X char(1),@Sum varchar(2)

WHILE @VIN > '' BEGIN
	INSERT INTO @Chars SELECT LEFT(@VIN,1),Weight FROM dbo.ts_VinCharWeight WHERE Char = LEFT(@VIN,1)
	SET @VIN = RIGHT(@VIN,LEN(@VIN)-1)
END

SELECT @X = Char FROM @Chars WHERE Pos = 9
SELECT @Sum = SUM(c.Weight*w.Weight) % 11 FROM @Chars c INNER JOIN dbo.ts_VinWeight w ON c.Pos = w.Pos

--SET @Ret = 
RETURN CASE @Sum WHEN 10
								 THEN CASE @X WHEN 'X' THEN 1 ELSE 0 END
								 ELSE CASE @X WHEN @Sum THEN 1 ELSE 0 END
			 END

--SELECT @X X,@Sum [Sum],@Ret Ret

--RETURN 1

END






